package database;

import gui.ExceptionEvent;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class PosData extends DataSuperclass {
	public PosData() {
		con = OracleConnection.getInstance().getConnection();
	}

	public boolean findReceiptAndUPC(int rec, int upc) {

		try {
			ps = con.prepareStatement("SELECT upc FROM purchase_item where upc = ? AND receipt_id = ?");
			ps.setInt(1, upc);
			ps.setInt(2, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
		
		public boolean findReturnAndUPC(int retId, int upc) {

			try {
				ps = con.prepareStatement("SELECT upc FROM return_item where upc = ? AND return_id = ?");
				ps.setInt(1, upc);
				ps.setInt(2, retId);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					return true;
				} else {
					return false;
				}
			
			} catch (SQLException ex) {
				ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
				fireExceptionGenerated(event);
				return false;
			}

	}

	public double getTotalPurchase(int rec) {

		try {
			ps = con.prepareStatement("SELECT sum(price*purchase_qty) FROM purchase_item join item on item.upc=purchase_item.upc WHERE receipt_id = ?");

			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getDouble(1);
			} else {
				return 0;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return 0;
		}

	}
	
	public double getTotalReturn(int retId) {

		try {
			ps = con.prepareStatement("SELECT sum(price*return_qty) FROM return_item join item on item.upc=return_item.upc WHERE return_id = ?");

			ps.setInt(1, retId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getDouble(1);
			} else {
				return 0;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return 0;
		}

	}
	
	public int getMaxReceipt() {
		try {
			ps = con.prepareStatement("SELECT max(receipt_id) FROM purchase");

			ResultSet rs = ps.executeQuery();
			rs.next();

			return rs.getInt(1);
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -1;
		}

	}
	
	public int getMaxRetId() {
		try {
			ps = con.prepareStatement("SELECT max(return_id) FROM return");
			
			ResultSet rs = ps.executeQuery();
			rs.next();
			
			return rs.getInt(1);
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			
			return -1;
		}
	}
	

	public ResultSet showTransaction(int rec) {
		try {
			ps = con.prepareStatement(
					"SELECT item.upc, title, purchase_qty, price, price*purchase_qty as Line_Total FROM purchase_item join item on item.upc=purchase_item.upc WHERE receipt_id = ?",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE); // you can't edit once you join
													// another table!?!
			ps.setInt(1, rec);

			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {

			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	
	public ResultSet showReturn(int retId) {
		try {
			ps = con.prepareStatement(
					"SELECT item.upc, title, return_qty, price, price*return_qty as Line_Total FROM return_item join item on item.upc=return_item.upc WHERE return_id = ?",
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			
			ps.setInt(1, retId);
			
			ResultSet rs = ps.executeQuery();
			return rs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}

	public boolean adjustPurItem(int rec, int upc, int qty) {
		try {

			ps = con.prepareStatement("UPDATE purchase_item SET purchase_qty = ? WHERE receipt_id = ? AND upc = ?");

			ps.setInt(1, qty);
			ps.setInt(2, rec);
			ps.setInt(3, upc);
			ps.executeQuery();

			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}
	
	public boolean adjustRetItem(int retId, int upc, int qty) {
		try {

			ps = con.prepareStatement("UPDATE return_item SET return_qty = ? WHERE return_id = ? AND upc = ?");

			ps.setInt(1, qty);
			ps.setInt(2, retId);
			ps.setInt(3, upc);
			ps.executeQuery();

			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}

	// use purchase date field to determine if transaction is paid for (create as null, set date when paid)
	// delivered date is set manually by manager later...
	public boolean isPurDateNull(int rec) {
		try {
			// get receipt id if ship date is null
			ps = con.prepareStatement("SELECT * from purchase where receipt_id = ? AND purchase_date IS NULL");
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			// if there is a result then true
			return rs.next();

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}

	public boolean isRetDateNull(int retId) {
		try {
			// get receipt id if ship date is null
			ps = con.prepareStatement("SELECT * from return where return_id = ? AND return_date IS NULL");
			ps.setInt(1, retId);
			ResultSet rs = ps.executeQuery();
			// if there is a result then true
			return rs.next();

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean setPurDate(int rec) {
		try {
			ps = con.prepareStatement("UPDATE purchase SET purchase_date = ? WHERE receipt_id = ?");
			ps.setDate(1, date());
			ps.setInt(2, rec);
			ps.executeQuery();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}

	public boolean setDelDate(int rec) {
		try {
			ps = con.prepareStatement("UPDATE purchase SET delivered_date = ? WHERE receipt_id = ?");
			ps.setDate(1, date());
			ps.setInt(2, rec);
			ps.executeQuery();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean setRetDate(int retId) {
		try {
			ps = con.prepareStatement("UPDATE return SET return_date = ? WHERE return_id = ?");
			ps.setDate(1, date());
			ps.setInt(2, retId);
			ps.executeQuery();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public Date getPurDate(int rec) {
		try {
			ps = con.prepareStatement("SELECT purchase_date FROM purchase WHERE receipt_id = ?");
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getDate(1);
			} else {
				return null;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return null;
		}
	}

	
	public boolean addPurItem(Integer rec, Integer upc, Integer qty) {

		try {
			// constraint: needs existing rec entry in purchase table
			// handled in application logic
			
			ps = con.prepareStatement("INSERT INTO purchase_item VALUES (?,?,?)");
			ps.setInt(1, rec);
			ps.setInt(2, upc);
			ps.setInt(3, qty);
			ps.executeUpdate();
			con.commit();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;

			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}

	public Date date() {
		String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
		String month = String.valueOf(Calendar.getInstance().get(Calendar.MONTH)+1);
		String day = String.valueOf(Calendar.getInstance().get(Calendar.DAY_OF_MONTH));
		
		if (Integer.parseInt(month) < 10 && Integer.parseInt(day) < 10) {
			return convertToDate(year + "-0" + month + "-0" + day);
		}
		
		if (Integer.parseInt(month) < 10) {
			return convertToDate(year + "-0" + month + "-" + day);
		} 
		
		if (Integer.parseInt(day) < 10) {
			return convertToDate(year + "-" + month + "0-" + day);
		}
	
		return convertToDate(year + "-" + month + "-" + day);	
	}
	
	public Date convertToDate(String s) {
		DateFormat d = new SimpleDateFormat("yyyy-MM-dd");
		java.util.Date date = null;
		try {
			date = d.parse(s);
		} catch (ParseException e) {
			System.out.println("somethingsomethingparsedate");
		}
		java.sql.Date sqlDate = new Date(date.getTime());
		return sqlDate;
		}

	public boolean addPurchase(int rec) {

		try {
			ps = con.prepareStatement("INSERT INTO purchase VALUES (?,null,null,null,null,null,null)");
			ps.setInt(1, rec);
			ps.executeUpdate();
			con.commit();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;
		
			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}
	
	
	public boolean addReturn(int retId, int rec) {
		try {
			ps = con.prepareStatement("INSERT INTO return VALUES (?,null,?)");
			ps.setInt(1, retId);
			ps.setInt(2, rec);
			ps.executeUpdate();
			con.commit();
			return true;
			
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			try {
				con.rollback();
				return false;

			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}
	
	
	public boolean addRetItem(Integer retId, Integer upc, Integer qty) {
		try {
			// constraint: needs existing retId entry in return table
			// handled in application logic
			
			ps = con.prepareStatement("INSERT INTO return_item VALUES (?,?,?)");
			ps.setInt(1, retId);
			ps.setInt(2, upc);
			ps.setInt(3, qty);
			ps.executeUpdate();
			con.commit();
			return true;

		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			try {
				con.rollback();
				return false;

			} catch (SQLException ex2) {
				event = new ExceptionEvent(this, ex2.getMessage());
				fireExceptionGenerated(event);
				return false;
			}
		}
	}
	
	public boolean isRecValid(int rec) {
		try {
			ps = con.prepareStatement("SELECT purchase_date FROM purchase where receipt_id = ?");
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean hasItemAndQty(int rec, int upc, int qty) {
		try {
			ps = con.prepareStatement("SELECT upc FROM purchase_item where receipt_id = ? AND upc = ? AND purchase_qty >= ?");
			ps.setInt(1, rec);
			ps.setInt(2, upc);
			ps.setInt(3, qty);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public int getStock(int upc) {
		try {
			ps = con.prepareStatement("SELECT stock FROM item WHERE upc = ?");
			ps.setInt(1, upc);
			
			ResultSet rs = ps.executeQuery();
			
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public boolean updateStockOnPurchase(int rec) {
		try {
			//ps = con.prepareStatement("UPDATE item SET stock = ? WHERE upc =?");
			Statement stm = con.createStatement();
			int upc = getPurUpc(rec);
			int qty = getPurQuantity(rec, upc);
			
			int i = getStock(upc) - qty;
			
			String qry = "UPDATE item SET stock = " + Integer.toString(i) +" WHERE upc =" + Integer.toString(upc);
			stm.executeUpdate(qry);
			/*
			ps.setInt(1, i);
			ps.setInt(2, upc);
			ps.executeUpdate();
			*/
			return true;
		}  catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public boolean updateStockOnReturn(int ret) {
		try {
			//ps = con.prepareStatement("UPDATE item SET stock = ? WHERE upc = ?");
			Statement stm = con.createStatement();
			int upc = getRetUpc(ret);
			int qty = getRetQuantity(ret);
			
			int i = getStock(upc) + qty;
			
			String qry = "UPDATE item SET stock = " + Integer.toString(i) +" WHERE upc =" + Integer.toString(upc);
			stm.executeUpdate(qry);
			
			/*
			ps.setInt(1, i);
			ps.setInt(2, upc);
			ps.executeUpdate();
			*/
			return true;
		}  catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return false;
		}
	}
	
	public int getRec(int retId) {
		try {
			ps = con.prepareStatement("SELECT receipt_id FROM return WHERE return_id = ?");
			ps.setInt(1, retId);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public int getPurUpc(int rec) {
		try {
			ps = con.prepareStatement("SELECT upc FROM purchase_item WHERE receipt_id = ?");
			ps.setInt(1, rec);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	
	
	public int getRetUpc(int retId) {
		try {
			ps = con.prepareStatement("SELECT upc FROM return_item WHERE return_id = ?");
			ps.setInt(1, retId);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public int getRetQuantity(int retId) {
		try {
			ps = con.prepareStatement("SELECT return_qty FROM return_item WHERE return_id = ?");
			ps.setInt(1, retId);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public int getPurQuantity(int rec, int upc) {
		try {
			ps = con.prepareStatement("SELECT purchase_qty FROM purchase_item WHERE receipt_id = ? AND upc = ?");
			ps.setInt(1, rec);
			ps.setInt(2, upc);
			
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return -2;
		}
	}
	
	public boolean updatePurchase(int retId) {
		try {
			Statement stm = con.createStatement();
			int rec = getRec(retId);
			int upc = getRetUpc(retId);
			int retQty = getRetQuantity(retId);
			int purQty = getPurQuantity(rec, upc);
			int qty = purQty - retQty;
			String qry = "UPDATE purchase_item SET purchase_qty = " + Integer.toString(qty) +" WHERE receipt_id =" + Integer.toString(rec) + " AND upc = " + Integer.toString(upc);
			stm.executeUpdate(qry);
			return true;

		} catch (SQLException ex) {
			ex.getMessage();
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);

			return false;
		}
	}
	
	public int showStock(int upc){
		try {
			ps = con.prepareStatement("SELECT stock FROM item WHERE upc = ?");
			ps.setInt(1, upc);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return -2;
		}
	}
	
	public List<Integer> getAllUpc(int rec) {
		try {
			ps = con.prepareStatement("SELECT upc FROM purchase_item WHERE receipt_id = ?");
			
			ps.setInt(1, rec);
			ResultSet rs = ps.executeQuery();
			List<Integer> upcs = new ArrayList<Integer>();
			while (rs.next()) {
				upcs.add(rs.getInt(1));
			}
			return upcs;
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return null;
		}
	}
	
	public double getPrice(int upc) {
		try {
			ps = con.prepareStatement("SELECT price FROM item WHERE upc = ?");
			ps.setInt(1, upc);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getDouble(1);
			} else {
				return -1;
			}
		} catch (SQLException ex) {
			ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
			fireExceptionGenerated(event);
			return -2;
		}
	}
	

	public String printReceipt(int rec) {
	String receipt = null;	
		
	String receiptId = "receipt #" + rec;
	String date = date().toString();
	List<Integer> upcs = getAllUpc(rec);
	List<Integer> qtys = new ArrayList<Integer>();
	List<Double> prices = new ArrayList<Double>();
	receipt = receiptId +"\n" + "Date: " + date +"\n";
	for (int i : upcs) {
		int q = getPurQuantity(rec, i);
		qtys.add(q);
		double p = getPrice(i);
		prices.add(p);
	}
	
	int j = 0;
	
	for (int i : upcs) {
		receipt = receipt + "UPC: " + i + "\n";
		int q = qtys.get(j);
		receipt = receipt + "Quantity: " + q + "\n";
		double p = prices.get(j)*q;
		receipt = receipt + "Price: $" + Double.toString(p) + "\n";
		j++;
	}
	
	int k = 0;
	
	double total = 0;
	
	for (double p : prices) {
		int q = qtys.get(k);
		total = total + p*q;
		k++;
	}
	
	receipt = receipt + "Total: $" + Double.toString(total) + "\n";
	
	return receipt;
		
	}

	public int daysBetween(Date d1, Date d2){
        return (int)( (d2.getTime() - d1.getTime()) / (1000 * 60 * 60 * 24));
	}
	
}
